/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Controleur.Participant;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 *
 * @author Valentin
 */
public class ParticipantDAO extends DAO<Participant> {

    public ParticipantDAO(Connection conn) {
        super(conn);

    }

    /**
     * insert un nouveau participant en base de données
     *
     * @param participant
     * @throws SQLException
     */
    @Override
    public void insert(Participant participant) throws SQLException {
        String query = " INSERT INTO"
                + " participants"
                + " (Nom, Prenom, Age, Departement)"
                + " VALUES (\"" + participant.getNom() + "\",\"" + participant.getPrenom() + "\"," + participant.getAge() + "," + participant.getDepartement() + ")";

        state.executeUpdate(query, state.RETURN_GENERATED_KEYS);
        try (ResultSet rs = state.getGeneratedKeys()) {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                do {
                    for (int i = 1; i <= columnCount; i++) {
                        int key = rs.getInt(i);
                        participant.setId(key);
                    }
                } while (rs.next());
            } else {
                participant.setId(-1);
            }
        }
    }

    /**
     * supprime un participant de la base de données
     *
     * @param participant
     * @throws SQLException
     */
    @Override
    public void delete(Participant participant) throws SQLException {
        String query = " DELETE FROM"
                + " participants"
                + " WHERE IDParticipant ="
                + participant.getId();

        state.executeUpdate(query);
    }

    @Override
    public void update(Participant participant) throws SQLException {
        String query = " UPDATE  participants"
                + " SET Nom = " + "\"" + participant.getNom() + "\""
                + " ,Prenom = " + "\"" + participant.getPrenom() + "\""
                + " ,Age = " + participant.getAge()
                + " ,Departement = " + participant.getDepartement()
                + " WHERE IDParticipant = " + participant.getId();

        state.executeUpdate(query);
    }

    @Override
    public Participant findByID(java.lang.Integer id) throws SQLException {
        Participant myParticipant = new Participant();
        String query = " SELECT *"
                + " FROM participants"
                + " WHERE IDParticipant = "
                + id;
        queryResult = state.executeQuery(query);

        while (queryResult.next()) {
            myParticipant.setId(Integer.decode(queryResult.getString("IDParticipant")));
            //Nom
            if (queryResult.getString("Nom") != null) {
                myParticipant.setNom(queryResult.getString("Nom"));
            } else {
                myParticipant.setNom("");
            }
            //Prenom
            if (queryResult.getString("Prenom") != null) {
                myParticipant.setPrenom(queryResult.getString("Prenom"));
            } else {
                myParticipant.setPrenom("");
            }
            //age
            if (queryResult.getString("Age") != null) {
                myParticipant.setAge(Integer.decode(queryResult.getString("Age")));
            } else {
                myParticipant.setAge(-1);
            }
            //departement
            if (queryResult.getString("Departement") != null) {
                myParticipant.setDepartement(Integer.decode(queryResult.getString("Departement")));
            } else {
                myParticipant.setDepartement(-1);
            }
        }
        if (myParticipant.getId() == 0) {
            myParticipant.setId(-1);
        }
        return myParticipant;
    }

    public Map<Integer, Participant> findByDepartement(int dept) throws SQLException {
        Map myMap = new HashMap();
        Participant myParticipant = new Participant();
        String query = " SELECT *"
                + " FROM participants"
                + " WHERE Departement = "
                + dept;

        queryResult = state.executeQuery(query);

        while (queryResult.next()) {
            myParticipant.setId(Integer.decode(queryResult.getString("IDParticipant")));
            //Nom
            if (queryResult.getString("Nom") != null) {
                myParticipant.setNom(queryResult.getString("Nom"));
            } else {
                myParticipant.setNom("");
            }
            //Prenom
            if (queryResult.getString("Prenom") != null) {
                myParticipant.setPrenom(queryResult.getString("Prenom"));
            } else {
                myParticipant.setPrenom("");
            }
            //age
            if (queryResult.getString("Age") != null) {
                myParticipant.setAge(Integer.decode(queryResult.getString("Age")));
            } else {
                myParticipant.setAge(-1);
            }
            //departement
            if (queryResult.getString("Departement") != null) {
                myParticipant.setDepartement(Integer.decode(queryResult.getString("Departement")));
            } else {
                myParticipant.setDepartement(-1);
            }

            myMap.put(myParticipant.getId(), myParticipant);
        }

        return myMap;
    }

    @Override
    public Map<Integer, Participant> findAll() throws SQLException {
        Map myMap = new HashMap();

        String query = " SELECT *"
                + " FROM participants"
                + " ORDER BY Nom asc";


        queryResult = state.executeQuery(query);

        while (queryResult.next()) {
            Participant myParticipant = new Participant();

            myParticipant.setId(Integer.decode(queryResult.getString("IDParticipant")));
            //Nom
            if (queryResult.getString("Nom") != null) {
                myParticipant.setNom(queryResult.getString("Nom"));
            } else {
                myParticipant.setNom("");
            }
            //Prenom
            if (queryResult.getString("Prenom") != null) {
                myParticipant.setPrenom(queryResult.getString("Prenom"));
            } else {
                myParticipant.setPrenom("");
            }
            //age
            if (queryResult.getString("Age") != null) {
                myParticipant.setAge(Integer.decode(queryResult.getString("Age")));
            } else {
                myParticipant.setAge(-1);
            }
            //departement
            if (queryResult.getString("Departement") != null) {
                myParticipant.setDepartement(Integer.decode(queryResult.getString("Departement")));
            } else {
                myParticipant.setDepartement(-1);
            }

            myMap.put(myParticipant.getId(), myParticipant);
        }

        return myMap;
    }

    public Map<Integer, Participant> findByExcursion(int IDExcursion) throws SQLException {

        Map myMap = new HashMap();        
        String query = " SELECT *"
                + " FROM participants p"
                + " LEFT JOIN inscription i on (i.IDParticipant_fk = p.IDParticipant) "
                + " LEFT JOIN excursion e on (e.IDExcursion = i.IDExcurtion_fk)";

        queryResult = state.executeQuery(query);


        while (queryResult.next()) {
            Participant myParticipant = new Participant();
            myParticipant.setId(queryResult.getInt("IDParticipant"));
            myParticipant.setNom(queryResult.getString("Nom"));
            myParticipant.setPrenom(queryResult.getString("Prenom"));
            myParticipant.setAge(queryResult.getInt("Age"));
            myParticipant.setDepartement(queryResult.getInt("Departement"));
            
            
            if (queryResult.getInt("IDExcurtion_fk")== IDExcursion){
                myParticipant.setInscrit(true);
                myParticipant.setLocation(queryResult.getBoolean("location"));
            }else{
                myParticipant.setInscrit(false);
                myParticipant.setLocation(false);
            }
                
            
            
            myMap.put(myParticipant.getId(), myParticipant);
        }
        return myMap;
    }

    public void inscription(Participant participant, int IDExcursion) throws SQLException {
        if (participant.isInscrit()){
        String query = " INSERT INTO "
                + " inscription"
                + " (IDExcurtion_fk,IDParticipant_fk,location)"
                + " VALUES (" + IDExcursion + ","
                + participant.getId() + ","+ participant.isLocation()+")"
                + " ON DUPLICATE KEY UPDATE"
                + " location = "+participant.isLocation();
        state.executeUpdate(query);
        }
        else{
            String query = " DELETE FROM"
                + " inscription"
                + " WHERE IDExcurtion_fk =" + IDExcursion
                + " AND IDParticipant_fk =" + participant.getId();

        state.executeUpdate(query);
        }
        
    }

   
    
    
}
